Custom Query

This property box allows you to insert a custom query to specify which table the Custom Query activity will search and which row's values it will return as tokens. This activity returns the values of the first row it finds as tokens. It also returns the collection of rows that have the specified column values.

Note: If this activity returns a collection of rows, you can access the tokens for each row with the For Each Row activity.

To configure Custom Query

1. Add the Custom Query activity to your workflow definition by dragging it from the Toolbox Pane and dropping it in the Designer Pane.
2. Select the activity in the Designer Pane.
3. Under Custom Query in the Properties Pane, click in the box below Query to create a custom query.



4. In the Custom Query Editor, enter your custom query under Custom Query.

Note: Different database sources have different syntaxes. The example below is written for the MSSQL ODBC. All queries are written in SQL. Use the correct syntax for your data source.



5. Optional: Type your Parameter Name under Parameter Names. How to specify the parameter names depends on how you are connecting to the data source:
• When using an ODBC connection, you cannot use named parameters. Use "?" for each parameter name. They are interpreted in the order in which they are listed. Show me what this looks like. You must list a parameter each time you use one in the query, even if you use the same parameter twice in the same query.



• With any other type of connection, use the naming convention specific to that connection. Named parameters can be used more than once in the query without being listed more than once in the parameter list. Show me what this looks like.



6. Optional: Type your Parameter Value under Parameter Values, or click the Token button (right arrow) to use tokens.

Tip: If you want a token to be replaced by the null value in the case that the token's value is blank (the empty string), apply the NULL token formatting expression. If you want to set a column's value to the null value regardless, insert the NULL token: %(DB.NULL).

7. Optional: Choose a type for your parameter from the Type drop-down menu. You will want to set a parameter type if you are invoking a stored procedure that expects a specific type. Also, not all database drivers/servers automatically convert strings to the desired parameter type, so you might want to manually set the type in that situation. In most cases, you will want to leave the type as default. More info.
8. When you enter a parameter name or value, an additional line appears for you type additional parameter names and values in. As you continue to type parameter names and values, additional lines appear.
9. Optional: Remove a parameter name and value by clicking the red X .
10. Click OK.
11. Optional: To modify your query, click Edit in the Custom Query property box, or click anywhere in the Query or Parameters boxes. Repeat steps 4 through 9.
12. Click the Test link to test your query.

Note: When using a SELECT query, you must test the query in order to make the columns accessible as tokens to the For Each Row activity.

1. In the Define Query Parameters dialog box, insert the value you want to test.
2. Click OK.
3. The Test Query dialog box displays how many rows the query will return or any error (with accompanying error message) it encounters.
4. Click OK.